package Checkin;
//@Jorge Neto

import LinkDB.LinkDB;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

public class RepositorioCheckin implements InterfaceCheckin {

    @Override
    public void inserirCheckin(String codreserva, String passaport, String ultprocedencia,
            String proxdestino, String checkin, String checkout, String numquarto,
            String cofre, String valordiaria, String acompanhantes, String observacoes,
            String formpagamento, String dadospagamento, String atendente) {

        //Conversão
        int refCodreserva = Integer.parseInt(codreserva);
        long refPassaport = Long.parseLong(passaport);
        SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy");
        java.sql.Date refCheckin = null;
        java.sql.Date refCheckout = null;

        try {
            refCheckin = new java.sql.Date(format.parse(checkin).getTime());
            refCheckout = new java.sql.Date(format.parse(checkout).getTime());

        } catch (ParseException ex) {
            //
        }

        float refValorDiaria = Float.parseFloat(valordiaria);
        int refAcompanhantes = Integer.parseInt(acompanhantes);

        Checkin chk = new Checkin(refCodreserva, refPassaport, ultprocedencia,
                proxdestino, refCheckin, refCheckout, numquarto, cofre,
                refValorDiaria, refAcompanhantes, observacoes, formpagamento,
                dadospagamento, atendente);

        Connection conn;
        String query;
        PreparedStatement stmt;

        try {
            conn = LinkDB.getConnection();

            query = "INSERT INTO checkin (codreserva, passaport, ultprocedencia,"
                    + "proxdestino, checkin, checkout, numquarto, cofre,"
                    + "valordiaria, acompanhantes, observacoes, formpagamento,"
                    + "dadospagamento, atendente) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            stmt = conn.prepareStatement(query);

            stmt.setInt(1, chk.getCodreserva());
            stmt.setLong(2, chk.getPassaport());
            stmt.setString(3, chk.getUltprocedencia().toUpperCase());
            stmt.setString(4, chk.getProxdestino().toUpperCase());
            stmt.setDate(5, chk.getCheckin());
            stmt.setDate(6, chk.getCheckout());
            stmt.setString(7, chk.getNumquarto());
            stmt.setString(8, chk.getCofre());
            stmt.setFloat(9, chk.getValordiaria());
            stmt.setInt(10, chk.getAcompanhantes());
            stmt.setString(11, chk.getObservacoes().toUpperCase());
            stmt.setString(12, chk.getFormpagamento());
            stmt.setString(13, chk.getDadospagamento());
            stmt.setString(14, chk.getAtendente().toUpperCase());

            stmt.executeUpdate();

            stmt.close();
            conn.close();


        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(RepositorioCheckin.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void alterarCheckin(String passaport, String ultprocedencia,
            String proxdestino, String checkin, String checkout, String numquarto,
            String cofre, String valordiaria, String acompanhantes, String observacoes,
            String formpagamento, String dadospagamento, String atendente, String codreserva) {

        //Conversão
        int refCodreserva = Integer.parseInt(codreserva);
        long refPassaport = Long.parseLong(passaport);
        SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy");
        java.sql.Date refCheckin = null;
        java.sql.Date refCheckout = null;

        try {
            refCheckin = new java.sql.Date(format.parse(checkin).getTime());
            refCheckout = new java.sql.Date(format.parse(checkout).getTime());

        } catch (ParseException ex) {
            //
        }
        float refValorDiaria = Float.parseFloat(valordiaria);
        int refAcompanhantes = Integer.parseInt(acompanhantes);

        Checkin chk = new Checkin(refCodreserva, refPassaport, ultprocedencia,
                proxdestino, refCheckin, refCheckout, numquarto, cofre,
                refValorDiaria, refAcompanhantes, observacoes, formpagamento,
                dadospagamento, atendente);

        Connection conn;
        String query;
        PreparedStatement stmt;

        try {
            conn = LinkDB.getConnection();

            query = "UPDATE checkin SET passaport = ?, ultprocedencia = ?,"
                    + "proxdestino = ?, checkin = ?, checkout = ?, numquarto = ?, cofre = ?,"
                    + "valordiaria = ?, acompanhantes = ?, observacoes = ?, formpagamento = ?,"
                    + "dadospagamento = ?, atendente = ? WHERE codreserva = ?";

            stmt = conn.prepareStatement(query);

            stmt.setLong(1, chk.getPassaport());
            stmt.setString(2, chk.getUltprocedencia().toUpperCase());
            stmt.setString(3, chk.getProxdestino().toUpperCase());
            stmt.setDate(4, chk.getCheckin());
            stmt.setDate(5, chk.getCheckout());
            stmt.setString(6, chk.getNumquarto());
            stmt.setString(7, chk.getCofre());
            stmt.setFloat(8, chk.getValordiaria());
            stmt.setInt(9, chk.getAcompanhantes());
            stmt.setString(10, chk.getObservacoes().toUpperCase());
            stmt.setString(11, chk.getFormpagamento());
            stmt.setString(12, chk.getDadospagamento());
            stmt.setString(13, chk.getAtendente());
            stmt.setInt(14, chk.getCodreserva());

            stmt.executeUpdate();

            stmt.close();
            conn.close();


        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(RepositorioCheckin.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void excluirCheckin(int codReserva) {

        Connection conn;
        String query;
        PreparedStatement stmt;

        try {
            conn = LinkDB.getConnection();

            query = "DELETE FROM checkin WHERE codreserva = ?";

            stmt = conn.prepareStatement(query);

            stmt.setInt(1, codReserva);

            stmt.executeUpdate();

            stmt.close();
            conn.close();

        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(RepositorioCheckin.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public ArrayList consultarCheckinPorCodReserva(String codReserva) {

        int refCodReserva = Integer.parseInt(codReserva);

        Connection conn;
        String query;
        PreparedStatement stmt;
        ResultSet rs;
        ArrayList list = new ArrayList();

        try {
            conn = LinkDB.getConnection();

            query = "SELECT * FROM checkin WHERE codreserva = ?";

            stmt = conn.prepareStatement(query);

            stmt.setInt(1, refCodReserva);

            rs = stmt.executeQuery();

            while (rs.next()) {

                list.add(rs.getInt("codreserva"));
                list.add(rs.getLong("passaport"));
                list.add(rs.getString("ultprocedencia"));
                list.add(rs.getString("proxdestino"));
                list.add(rs.getDate("checkin"));
                list.add(rs.getDate("checkout"));
                list.add(rs.getString("numquarto"));
                list.add(rs.getString("cofre"));
                list.add(rs.getFloat("valordiaria"));
                list.add(rs.getInt("acompanhantes"));
                list.add(rs.getString("observacoes"));
                list.add(rs.getString("formpagamento"));
                list.add(rs.getString("dadospagamento"));
                list.add(rs.getString("atendente"));

                return list;
            }
            stmt.executeQuery();
            stmt.close();

            conn.close();

        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(RepositorioCheckin.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public ArrayList consultarCheckinPorPassaport(String passaport) {

        long refPassaport = Long.parseLong(passaport);

        Connection conn;
        String query;
        PreparedStatement stmt;
        ResultSet rs;
        ArrayList list = new ArrayList();

        try {
            conn = LinkDB.getConnection();

            query = "SELECT * FROM checkin WHERE passaport = ?";

            stmt = conn.prepareStatement(query);

            stmt.setLong(1, refPassaport);

            rs = stmt.executeQuery();

            while (rs.next()) {

                list.add(rs.getInt("codreserva"));
                list.add(rs.getLong("passaport"));
                list.add(rs.getString("ultprocedencia"));
                list.add(rs.getString("proxdestino"));
                list.add(rs.getDate("checkin"));
                list.add(rs.getDate("checkout"));
                list.add(rs.getString("numquarto"));
                list.add(rs.getString("cofre"));
                list.add(rs.getFloat("valordiaria"));
                list.add(rs.getInt("acompanhantes"));
                list.add(rs.getString("observacoes"));
                list.add(rs.getString("formpagamento"));
                list.add(rs.getString("dadospagamento"));
                list.add(rs.getString("atendente"));

                return list;
            }

            stmt.executeQuery();
            stmt.close();

            conn.close();

        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(RepositorioCheckin.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public ArrayList consultarCheckinPorDataEntrada(String dataEntrada) {

        SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy");
        java.sql.Date refCheckin = null;
        try {
            refCheckin = new java.sql.Date(format.parse(dataEntrada).getTime());
        } catch (ParseException ex) {
            //
        }

        Connection conn;
        String query;
        PreparedStatement stmt;
        ResultSet rs;
        ArrayList list = new ArrayList();

        try {
            conn = LinkDB.getConnection();

            query = "SELECT * FROM checkin WHERE checkin = ?";

            stmt = conn.prepareStatement(query);

            stmt.setDate(1, refCheckin);

            rs = stmt.executeQuery();

            while (rs.next()) {

                list.add(rs.getInt("codreserva"));
                list.add(rs.getLong("passaport"));
                list.add(rs.getString("ultprocedencia"));
                list.add(rs.getString("proxdestino"));
                list.add(rs.getDate("checkin"));
                list.add(rs.getDate("checkout"));
                list.add(rs.getString("numquarto"));
                list.add(rs.getString("cofre"));
                list.add(rs.getFloat("valordiaria"));
                list.add(rs.getInt("acompanhantes"));
                list.add(rs.getString("observacoes"));
                list.add(rs.getString("formpagamento"));
                list.add(rs.getString("dadospagamento"));
                list.add(rs.getString("atendente"));

                return list;
            }

            stmt.executeQuery();
            stmt.close();

            conn.close();

        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(RepositorioCheckin.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public ArrayList consultarCheckinPorDataSaida(String dataSaida) {

        SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy");
        java.sql.Date refCheckout = null;
        try {
            refCheckout = new java.sql.Date(format.parse(dataSaida).getTime());
        } catch (ParseException ex) {
            //
        }

        Connection conn;
        String query;
        PreparedStatement stmt;
        ResultSet rs;
        ArrayList list = new ArrayList();

        try {
            conn = LinkDB.getConnection();

            query = "SELECT * FROM checkin WHERE checkout = ?";

            stmt = conn.prepareStatement(query);

            stmt.setDate(1, refCheckout);

            rs = stmt.executeQuery();

            while (rs.next()) {

                list.add(rs.getInt("codreserva"));
                list.add(rs.getLong("passaport"));
                list.add(rs.getString("ultprocedencia"));
                list.add(rs.getString("proxdestino"));
                list.add(rs.getDate("checkin"));
                list.add(rs.getDate("checkout"));
                list.add(rs.getString("numquarto"));
                list.add(rs.getString("cofre"));
                list.add(rs.getFloat("valordiaria"));
                list.add(rs.getInt("acompanhantes"));
                list.add(rs.getString("observacoes"));
                list.add(rs.getString("formpagamento"));
                list.add(rs.getString("dadospagamento"));
                list.add(rs.getString("atendente"));

                return list;
            }

            stmt.executeQuery();
            stmt.close();

            conn.close();

        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(RepositorioCheckin.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;

    }

    @Override
    public ArrayList consultarCheckinPorNumQuarto(String numQuarto) {

        Connection conn;
        String query;
        PreparedStatement stmt;
        ResultSet rs;
        ArrayList list = new ArrayList();

        try {
            conn = LinkDB.getConnection();

            query = "SELECT * FROM checkin WHERE numquarto = ?";

            stmt = conn.prepareStatement(query);

            stmt.setString(1, numQuarto);

            rs = stmt.executeQuery();

            while (rs.next()) {

                list.add(rs.getInt("codreserva"));
                list.add(rs.getLong("passaport"));
                list.add(rs.getString("ultprocedencia"));
                list.add(rs.getString("proxdestino"));
                list.add(rs.getDate("checkin"));
                list.add(rs.getDate("checkout"));
                list.add(rs.getString("numquarto"));
                list.add(rs.getString("cofre"));
                list.add(rs.getFloat("valordiaria"));
                list.add(rs.getInt("acompanhantes"));
                list.add(rs.getString("observacoes"));
                list.add(rs.getString("formpagamento"));
                list.add(rs.getString("dadospagamento"));
                list.add(rs.getString("atendente"));

                return list;
            }

            stmt.executeQuery();
            stmt.close();

            conn.close();

        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(RepositorioCheckin.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;

    }

    @Override
    public ArrayList consultarCheckinPorAtendente(String atendente) {

        Connection conn;
        String query;
        PreparedStatement stmt;
        ResultSet rs;
        ArrayList list = new ArrayList();

        try {
            conn = LinkDB.getConnection();

            query = "SELECT * FROM checkin WHERE atendente = ?";

            stmt = conn.prepareStatement(query);

            stmt.setString(1, atendente);

            rs = stmt.executeQuery();

            while (rs.next()) {

                list.add(rs.getInt("codreserva"));
                list.add(rs.getLong("passaport"));
                list.add(rs.getString("ultprocedencia"));
                list.add(rs.getString("proxdestino"));
                list.add(rs.getDate("checkin"));
                list.add(rs.getDate("checkout"));
                list.add(rs.getString("numquarto"));
                list.add(rs.getString("cofre"));
                list.add(rs.getFloat("valordiaria"));
                list.add(rs.getInt("acompanhantes"));
                list.add(rs.getString("observacoes"));
                list.add(rs.getString("formpagamento"));
                list.add(rs.getString("dadospagamento"));
                list.add(rs.getString("atendente"));

                return list;
            }

            stmt.executeQuery();
            stmt.close();

            conn.close();

        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(RepositorioCheckin.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;

    }

    public int pegarUltimoRegistro() {

        Connection conn;
        PreparedStatement stmt;
        ResultSet rs;
        String query;

        try {
            conn = LinkDB.getConnection();

            query = "SELECT MAX(codreserva) FROM checkin";

            stmt = conn.prepareStatement(query);

            rs = stmt.executeQuery();

            int a = 0;

            while (rs.next()) {
                a = rs.getInt(1);
            }
            return a;

        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(RepositorioCheckin.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }

    public ArrayList pegarAtendente(boolean status) {

        Connection conn;
        String query;
        PreparedStatement stmt;
        ResultSet rs;

        try {

            conn = LinkDB.getConnection();

            query = "SELECT nome FROM acesso WHERE status = ?";

            stmt = conn.prepareStatement(query);

            stmt.setBoolean(1, status);
            rs = stmt.executeQuery();

            ArrayList list = new ArrayList();

            while (rs.next()) {
                list.add(rs.getString("nome"));
            }
            return list;
        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(RepositorioCheckin.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public ArrayList pegarPassaport() {
        Connection conn;
        String query;
        PreparedStatement stmt;
        ResultSet rs;

        try {

            conn = LinkDB.getConnection();

            query = "SELECT cpf FROM cliente order by id DESC";

            stmt = conn.prepareStatement(query);

            rs = stmt.executeQuery();

            ArrayList list = new ArrayList();

            while (rs.next()) {
                list.add(rs.getString("cpf"));
            }
            return list;
        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(RepositorioCheckin.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
}
